#include "cConnection.h"
#include "cCrypto.h"
#include "cDatabase.h"
#include "NetSock.h"

// connection will be broken when client doesn't reply to server within this peroid
#define PACKET_TIMEOUT_MS 8000
// keepalive will be sent after this peroid without any received packet
#define NO_PACKET_TIMEOUT_MS 10000

extern std::vector<cConnection*> connections; // from main.cpp

cConnection::cConnection( NetSock* s ) {
	ASSERT(s);
	log_net_debug2("cConnection constructor\n");
	Closed = false;
	s->SetMode(NetSock::ASYNCHRONIC);

	loggedIn = false;

	_socket = s;
	_thread = new std::thread(cConnection::threadfunc, this);
}

cConnection::~cConnection() {
	if(_socket != nullptr) {
		log_net_debug( "cConnection destructor - socket's still there\n" );
		return;
	}
	log_net_debug2( "cConnection destructor\n" );
	//delete _thread;
}

bool cConnection::QueuePacket( cPacketBase* packet ) {
	queueMutex.lock();
	if( packetQueue.size() > MAX_PACKETS_IN_QUEUE ) return false;
#if _DEBUG
	if( packetQueue.size() > 8 ) LOG2( "Packet queue big. Packets in queue: %d\n", packetQueue.size() );
	if( !cPacketBase::isValidID(packet->GetID()) ) LOG2( "Packet with invalid ID added to queue!\n" );
#endif
	packetQueue.push_back( packet );
	queueMutex.unlock();
	return true;
}


void cConnection::ClearQueue() {
	queueMutex.lock();
	packetQueue.clear();
	queueMutex.unlock();
}


std::string cConnection::GetSocketString() {
	char buf[64];
	unsigned int ip = _socket->GetIP();
	sprintf_s(buf, "%d.%d.%d.%d:%d", ip & 0xff, (ip & 0xff00) >> 8, 
				(ip & 0xff0000) >> 16, (ip & 0xff000000) >> 24, _socket->GetPort());

	std::string str (buf);
	return str;
}



std::string cConnection::readString() {
	unsigned int len = 0;
	unsigned short len2 = 0;
	_socket->Read(&len2,2);
	len = ntohs(len2);
	if(len & 0x8000) {
		len *= 0x10000;
		_socket->Read(&len2,2);
		len += len2;
	}
	char* str = new char[len+1];
	_socket->Read(str, len);
	str[len] = '\0';
	return std::string( str );
}

unsigned char cConnection::readByte() {
	unsigned char c;
	_socket->Read( &c, 1 );
	return c;
}

unsigned short cConnection::readShort() {
	unsigned short s;
	_socket->Read( &s, 2 );
	s = ntohs( s );
	return s;
}

unsigned int cConnection::readInt() {
	unsigned int i;
	_socket->Read( &i, 4 );
	i = ntohl( i );
	return i;
}

void cConnection::doCleanup() {
	if(loggedIn) {
		char query[200];
		long long duration = time(0) - sessionStart;
		sprintf_s(query, "insert into `main`.`loginhistory` (`userid`, `logindate`, `duration`) values (%lld, %lld, %lld)", 
							userid, sessionStart, duration);
		cDatabase::Get()->Query(query);
	}

	delete _socket;
	_socket = nullptr;
	Closed = true;
}

void cConnection::threadfunc( cConnection* conn ) { 
	NetSock* sock = conn->_socket;
	std::this_thread::sleep_for( std::chrono::milliseconds(25) ); // allow first packets to queue (maybe a terminator, so we terminate before read if needed)
	bool busy = true;
	int timeout_counter = 0;
	bool keepalive_sent = false;
	for(;;) {
		if(conn->Closed) {
			LOG2("Connection closed! (conn->Closed = true)\n");
			return;
		}
		std::this_thread::sleep_for( std::chrono::milliseconds(busy ? 2 : 50) );
		timeout_counter += busy ? 2 : 50;
		busy = false;
		
		
		
		if(timeout_counter > PACKET_TIMEOUT_MS + NO_PACKET_TIMEOUT_MS) {
			conn->QueuePacket(new PACKET_TERMINATOR());
		}else if(timeout_counter > NO_PACKET_TIMEOUT_MS && !keepalive_sent) {
			keepalive_sent = true;
			conn->QueuePacket(new PACKET_KEEPALIVE());
		}

		sock->SetMode(NetSock::ASYNCHRONIC);

		while(conn->packetQueue.size() > 0) {
			busy = true;
			//timeout_counter = 0;
			conn->queueMutex.lock();
			cPacketBase* pkt = conn->packetQueue.front();
			conn->packetQueue.pop_front();
			conn->queueMutex.unlock();

			if( pkt->GetID() == PACKET_TERMINATOR_ID ) {
				log_net_debug2("Terminator packet (%s)\n", conn->GetSocketString().c_str());
				conn->doCleanup();
				return;
			}

			packet_data data = pkt->getStream().getBuffer();
			
#if NET_DEBUG >= 2
			log_net_debug2("Sending to %s:\n", conn->GetSocketString().c_str());
			hex_dump(data.data, data.length);
#endif
			if( sock->WriteAll(data.data, data.length) != (int)data.length ) {
				printf("Connection from %s terminated - write error.\n", conn->GetSocketString().c_str());
				conn->doCleanup();
				return;
			}

			delete pkt;

		}
		
		// read
		{
			unsigned char packet_id;
			int readsize = sock->Read(&packet_id, 1);
			if(readsize == 0 || (readsize == 1 && packet_id == 0)) {
				// onDisconnected
				printf("Connection from %s terminated by client.\n", conn->GetSocketString().c_str());
				conn->doCleanup();
				return;
			}else if(readsize == 1) { // data
				busy = true;
				timeout_counter = 0;
				keepalive_sent = false;

				sock->SetMode(NetSock::SYNCHRONIC);

				switch( packet_id ) {
				case CPKT_KEEPALIVE_ID: // do nothing
					LOG("Keepalive recvd\n");
					break;
				case CPKT_LOGIN_ID: 
					{
						srand((unsigned int)( time(NULL) + (int)(sock) + (int)(conn) ));
						//todo
						std::string username = conn->readString();
						bool b1 = conn->readByte() != 0;
						if( !b1 ) {
							conn->loginSalt = std::string("a6sd86av8sd6v8a6s");
							conn->QueuePacket( new PACKET_LOGIN( conn->loginSalt ) );
						}else{
							unsigned char buf[32];
							unsigned char clientbuf[32];
							int clilen = conn->readShort(); // SYNC
							if(clilen != 32) { 
								ERRORLOG("cConnection -> login -> clilen != 32");
								conn->QueuePacket(new PACKET_DISCONNECT("Internal error has occured. Please try again. If the problem persists, please contact your system administrator."));
								conn->QueuePacket(new PACKET_LOGIN( false ));
								conn->QueuePacket(new PACKET_TERMINATOR());
								break;
							}
							sock->Read(clientbuf, 32);
							
							char* username_c = (char*)username.c_str();
							char* startpos = username_c;
							while(*username_c != '\0') {
								if(*username_c < '0' || (*username_c > '9' && *username_c < 'A') || (*username_c > 'Z' && *username_c < 'a') || *username_c > 'z' || (username_c - startpos > 32)) {
									conn->QueuePacket(new PACKET_LOGIN( false ));
									conn->QueuePacket(new PACKET_TERMINATOR());
									break;
								}
								username_c++;
							}
							username_c = startpos;
							char query[250];
							sprintf_s(query, "select passhash, `id`, accessgroup, teacherid, `name`, username, chataccess from users where username='%s' and accessgroup < 10 limit 1", (const char*)username_c);
							DBResult res = cDatabase::Get()->Query(query);
							if(res.GetRows() != 1) {
								std::this_thread::sleep_for( std::chrono::milliseconds(1000) );
								conn->QueuePacket(new PACKET_LOGIN( false ));
								conn->QueuePacket(new PACKET_TERMINATOR());
								break;
							}
							
							std::string dbPass = res.data[0][0];
							
							cCrypto::SHA_256((unsigned char*)(dbPass + conn->loginSalt).c_str(), (dbPass + conn->loginSalt).length(), buf);
							if( memcmp(buf, clientbuf, 32) != 0 ) {
								std::this_thread::sleep_for( std::chrono::milliseconds(1000) );
								conn->QueuePacket(new PACKET_LOGIN( false ));
								conn->QueuePacket(new PACKET_TERMINATOR());
								break;
							}else conn->QueuePacket(new PACKET_LOGIN( true ));

							conn->userid = atol( res.data[0][1].c_str() );
							conn->accessgroup = atoi( res.data[0][2].c_str() );
							conn->teacherid = atoi( res.data[0][3].c_str() );
							conn->fullname = res.data[0][4];
							conn->username = res.data[0][5];
							conn->chatAccess = atoi( res.data[0][6].c_str() ) != 0;
							conn->sessionStart = time(0);
							conn->loggedIn = true;
						}
						
						
					}
					break;

				case CPKT_SEARCH_USERS_ID:
					{
						std::string searched = conn->readString();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						if(searched.length() > 0) {
							cDBStatement* stmt = cDatabase::Get()->PrepareQuery(
								"select u.id, u.username, u.name, u.accessgroup, t.name "
								"from users u, users t "
								"where u.accessgroup < 10 and u.name like ? and t.id = CASE WHEN u.teacherid < 0 THEN 1 ELSE u.teacherid END");
							if(stmt != nullptr) {
								stmt->bindString( 1, "%" + searched + "%" );
								DBResult res = stmt->execute();

								if(res.GetRows() < 1) {
									conn->QueuePacket(new PACKET_MESSAGE("No users found."));
									conn->QueuePacket(new PACKET_USERMNGLIST());
								}else{
									PACKET_USERMNGLIST* pkt = new PACKET_USERMNGLIST();
									for(int i = 0; i < res.GetRows(); i++)
										pkt->addUser(stol(res.data[i][0]), res.data[i][2], res.data[i][1], res.data[i][4], stoi(res.data[i][3]));
									conn->QueuePacket(pkt);
								}
								delete stmt;
							}
						}else{
							DBResult res = cDatabase::Get()->Query(
								"select u.id, u.username, u.name, u.accessgroup, t.name "
								"from users u, users t "
								"where u.accessgroup < 10 and t.id = CASE WHEN u.teacherid < 0 THEN 1 ELSE u.teacherid END");

							if(res.GetRows() < 1) {
								conn->QueuePacket(new PACKET_MESSAGE("No users found."));
								conn->QueuePacket(new PACKET_USERMNGLIST());
							}else{
								PACKET_USERMNGLIST* pkt = new PACKET_USERMNGLIST();
								for(int i = 0; i < res.GetRows(); i++)
									pkt->addUser(stol(res.data[i][0]), res.data[i][2], res.data[i][1], res.data[i][4], stoi(res.data[i][3]));
								conn->QueuePacket(pkt);
							}
						}
					}
					break;
				case CPKT_RESET_PASS_ID:
					{
						int user = conn->readInt();
						std::string password = conn->readString();

						if(!conn->loggedIn || (conn->accessgroup < 2 && conn->userid != user))
							break;

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("select 1 from users where id = ? limit 1");
						if(stmt != nullptr) {
							stmt->bindInt( 1, user );
							DBResult res = stmt->execute();
							delete stmt;
							if(res.GetRows() != 1) {
								conn->QueuePacket(new PACKET_MESSAGE("An error has occured while changing the password."));
								break;
							}
						}
						stmt = cDatabase::Get()->PrepareQuery( "update users set passhash = ? where id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 2, user );
							stmt->bindString( 1, password );
							stmt->execute();
							delete stmt;
						}

					}
					break;
				case CPKT_CREATE_USER_ID:
					{
						std::string fullname = conn->readString();
						std::string username = conn->readString();
						std::string password = conn->readString();
						std::string teacher = conn->readString();
						int group = conn->readByte();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("select id from users where username = ? limit 1");
						if(stmt != nullptr) {
							stmt->bindString( 1, username );
							DBResult res = stmt->execute();
							delete stmt;
							if(res.GetRows() > 0) {
								conn->QueuePacket(new PACKET_MESSAGE("An user with this username already exists."));
								break;
							}
						}
						int teacherid = -1;
						if(teacher != "") {
							stmt = cDatabase::Get()->PrepareQuery("select id from users where username = ? limit 1");
							if(stmt != nullptr) {
								stmt->bindString( 1, teacher );
								DBResult res = stmt->execute();
								if(res.GetRows() != 1) {
									conn->QueuePacket(new PACKET_MESSAGE("Teacher with given username does not exist."));
									break;
								}
								teacherid = stoi(res.data[0][0].c_str());
								delete stmt;
							}
						}
						stmt = cDatabase::Get()->PrepareQuery("INSERT INTO `main`.`users` (username, passhash, name, teacherid, accessgroup, chataccess) VALUES ( ?, ?, ?, ?, ?, 1 )");
						if(stmt != nullptr) {
							stmt->bindString( 1, username );
							stmt->bindString( 2, password );
							stmt->bindString( 3, fullname );
							stmt->bindInt( 4, teacherid );
							stmt->bindInt( 5, group );
							stmt->execute();
							delete stmt;
						}
					}
					break;
				case CPKT_REMOVE_USER_ID:
					{
						unsigned int userid = conn->readInt();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery(
							"update users set passhash = 'user-deleted', accessgroup = 11 where id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, userid );
							stmt->execute();
							delete stmt;
						}
					}
					break;
				case CPKT_SET_USER_GROUP_ID:
					{
						unsigned int userid = conn->readInt();
						int group = conn->readByte();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery(
							"update users set accessgroup = ? where id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, group );
							stmt->bindInt( 2, userid );
							stmt->execute();
						}
						LOG("Setting user %d to group %d\n", userid, group);
					}
					break;
				case CPKT_SELF_INFO_ID:
					{
						int flags = conn->readByte();
						bool name_user = (flags & 0x01) != 0;
						bool name_teacher = (flags & 0x02) != 0;
						bool login_date = (flags & 0x04) != 0;
						//bool user_stats = (flags & 0x08) != 0;
						bool access_group = (flags & 0x10) != 0;
						bool chat_rights = (flags & 0x20) != 0;
						bool b_user_id = (flags & 0x40) != 0;

						PACKET_SELF_INFO* pkt = new PACKET_SELF_INFO();
						if(name_user) pkt->username = &conn->fullname;
						if(access_group) pkt->user_group = conn->accessgroup;
						if(chat_rights) pkt->chat_access = conn->chatAccess;
						if(b_user_id) pkt->user_id = (int)conn->userid;

						if(name_teacher) {
							if( conn->teacherid < 0 ) {
								pkt->teachername = new std::string( "[n/a]" );
							}else{
								cDBStatement* stmt = cDatabase::Get()->PrepareQuery("select name from users where id = ? limit 1");
								if(stmt != nullptr) {
									stmt->bindInt( 1, (int)conn->teacherid );
									DBResult res = stmt->execute();
									if(res.GetRows() != 1) {
										pkt->teachername = new std::string( "[n/a]" );
										LOG("error! teacheid %d not found!\n", conn->teacherid);
									}else{
										pkt->teachername = new std::string( res.data[0][0] );
									}
									delete stmt;
								}
							}
						}

						if( login_date ) {
							cDBStatement* stmt = cDatabase::Get()->PrepareQuery(
								"select (logindate + duration) from loginhistory where userid = ? order by logindate desc limit 1");
							if(stmt != nullptr) {
								stmt->bindInt( 1, (int)conn->userid );
								DBResult res = stmt->execute();
								if(res.GetRows() == 1) {
									pkt->last_login = atol (res.data[0][0].c_str());
								}
								delete stmt;
							}
						}


						conn->QueuePacket( pkt );
					}
					break;
				case CPKT_ADMIN_STATS_ID:
					{
						unsigned int userid = conn->readInt();
						
						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cStream str;
						str.writeByte(4); //stats packet

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery(
							"select logindate, duration from loginhistory where userid = ? order by logindate desc limit 1000");

						if(stmt != nullptr) {
							stmt->bindInt( 1, userid );
							DBResult res = stmt->execute();
							delete stmt;
							str.writeInt(res.GetRows());
							for(int i = 0; i < res.GetRows(); i++) {
								str.writeInt(atoi(res.data[i][0].c_str()));
								str.writeInt(atoi(res.data[i][1].c_str()));
							}
						}
						

						struct test_st {
							int resultid;
							std::string name;
							bool isHomework;
							int dateDue;
							int dateDone;
							int score;
						};

						std::vector<test_st> tests;

						stmt = cDatabase::Get()->PrepareQuery(
							"select t1.datetaken, t1.score, t2.title, t1.id from testresults t1, tests t2 where t1.userid = ? and t2.id = t1.testid order by t1.datetaken desc");
						if(stmt != nullptr) {
							stmt->bindInt( 1, userid );
							DBResult res = stmt->execute();
							delete stmt;
							for(int i = 0; i < res.GetRows(); i++) {
								test_st t;
								t.isHomework = false;
								t.dateDue = 0;
								t.score = atoi(res.data[i][1].c_str());
								t.dateDone = atoi(res.data[i][0].c_str());
								t.name = res.data[i][2];
								t.resultid = atoi(res.data[i][3].c_str());
								tests.push_back(t);
							}
						}

						std::vector<test_st> final_tests;

						int totalResults = (int)tests.size();

						stmt = cDatabase::Get()->PrepareQuery(
							"select t2.title, t1.dateset, t1.datedue, t1.resultid from homework t1, tests t2 where t2.id = t1.testid and t1.userid = ? order by t1.datedue desc");
						if(stmt != nullptr) {
							stmt->bindInt( 1, userid );
							DBResult res = stmt->execute();
							delete stmt;
							for(int i = 0; i < res.GetRows(); i++) {
								int res_id = atoi(res.data[i][3].c_str()); 
								test_st test;
								if(res_id > 0) { // done
									totalResults--;
									for(size_t j = 0; j < tests.size(); j++) {
										test_st t = tests[j];
										if(t.resultid == res_id) {
											test.dateDone = t.dateDone;
											test.isHomework = true;
											test.dateDue = atoi(res.data[i][2].c_str());
											test.name = t.name;
											test.score = t.score;
											test.resultid = 0; // not needed anymore
											tests[j].resultid = 0; // exclude from sending
											totalResults++;
										}
									}
								}else{ // not done
									totalResults++;
									test.dateDue = atoi(res.data[i][2].c_str());
									test.dateDone = 0;
									test.isHomework = true;
									test.name = res.data[i][0];
									test.score = 0;
									test.resultid = 0;
								}
								final_tests.push_back(test);
							}
						}

						str.writeInt(totalResults);

						for(size_t i = 0; i < tests.size(); i++) {
							if(tests[i].resultid > 0) {
								str.writeString(tests[i].name);
								str.writeInt(0); // date due 0
								str.writeInt(tests[i].dateDone);
								str.writeShort((unsigned short)tests[i].score);
							}
						}
						for(size_t i = 0; i < final_tests.size(); i++) {
							str.writeString(final_tests[i].name);
							str.writeInt(final_tests[i].dateDue);
							str.writeInt(final_tests[i].dateDone);
							str.writeShort((unsigned short)final_tests[i].score);
						}

						cRawPacket* pkt = new cRawPacket(str);
						conn->QueuePacket(pkt);
					}
					break;
				case CPKT_TEST_LIST_ID:
				case CPKT_TEST_LIST_ID_ALT:
					{
						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cStream str;
						str.writeByte( packet_id == CPKT_TEST_LIST_ID ? 9 : 11); //stats packet

						DBResult res = cDatabase::Get()->Query(
							"select id, title, dateadded from tests order by dateadded desc");

						str.writeInt(res.GetRows());
						for(int i = 0; i < res.GetRows(); i++) {
							str.writeInt(atoi(res.data[i][0].c_str()));
							str.writeString(res.data[i][1]);
							str.writeInt(atoi(res.data[i][2].c_str()));
						}
						

						cRawPacket* pkt = new cRawPacket(str);
						conn->QueuePacket(pkt);
					}
					break;
				case CPKT_ADD_TEST_ID:
					{
						std::string testname = conn->readString();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						if(testname.length() < 3) {
							conn->QueuePacket(new PACKET_MESSAGE("Please enter valid test name. (at least 3 characters)"));
							break;
						}

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("INSERT INTO tests (title, dateadded) VALUES ( ?, ? )");
						if(stmt != nullptr) {
							stmt->bindString( 1, testname );
							stmt->bindInt( 2, (int)time(0) );
							stmt->execute();
							delete stmt;
						}
					}
					break;
				case CPKT_TEST_REMOVE_ID:
					{
						int testid = conn->readInt();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("DELETE FROM tests WHERE id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							stmt->execute();
							delete stmt;
						}

						stmt = cDatabase::Get()->PrepareQuery("DELETE FROM testquestions WHERE testid = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							stmt->execute();
							delete stmt;
						}

					}
					break;
				case CPKT_TEST_QUESTION_ADD_ID:
					{
						int testid = conn->readInt();
						std::string question = conn->readString();
						std::string a1 = conn->readString();
						std::string a2 = conn->readString();
						std::string a3 = conn->readString();
						std::string a4 = conn->readString();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("SELECT 1 FROM tests WHERE id = ? LIMIT 1");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							if(stmt->execute().GetRows()<1) {
								conn->QueuePacket(new PACKET_MESSAGE("This test doesn't exist anymore!"));
								delete stmt;
								break;
							}
							delete stmt;
						}

						stmt = cDatabase::Get()->PrepareQuery("INSERT INTO testquestions (testid, question, questiontype, answer) VALUES ( ?, ?, 0, ? )");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							stmt->bindString( 2, question );
							stmt->bindString( 3, a1 + ";" + a2 + ";" + a3 + ";" + a4 );
							stmt->execute();
							delete stmt;
						}

					}
					break;
				case CPKT_TEST_QUESTION_REMOVE_ID:
					{
						int qid = conn->readInt();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("DELETE FROM testquestions WHERE id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, qid );
							stmt->execute();
							delete stmt;
						}

					}
					break;
				case CPKT_TEST_QUESTION_LIST_ID:
					{
						int testid = conn->readInt();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;

						cStream str;
						str.writeByte(10);

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("SELECT title FROM tests WHERE id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							DBResult res = stmt->execute();
							delete stmt;
							if(res.GetRows() > 0)
								str.writeString(res.data[0][0]);
							else {
								conn->QueuePacket(new PACKET_MESSAGE("This test doesn't exist anymore!"));
								break;
							}
						}

						stmt = cDatabase::Get()->PrepareQuery("SELECT id, question FROM testquestions WHERE testid = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							DBResult res = stmt->execute();
							delete stmt;
							str.writeInt(res.GetRows());
							for(int i = 0; i < res.GetRows(); i++) {
								str.writeInt(atoi(res.data[i][0].c_str()));
								str.writeString(res.data[i][1]);
							}
						}

						cRawPacket* pkt = new cRawPacket(str);
						conn->QueuePacket(pkt);
					
					}
					break;
				case CPKT_SET_HOMEWORK_ID:
					{
						int userid = conn->readInt();
						int testid = conn->readInt();
						int datedue = conn->readInt();

						if(!conn->loggedIn || conn->accessgroup < 2)
							break;


						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("SELECT dateset, datedue, resultid FROM homework WHERE testid = ? AND userid = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							stmt->bindInt( 2, userid );
							DBResult res = stmt->execute();
							delete stmt;
							if(res.GetRows() > 0) {
								bool done = true;
								for(int i = 0; i < res.GetRows(); i++ )
									if(atoi(res.data[i][2].c_str()) < 0) {
										done = false;
										break;
									}
								if(!done) {
									conn->QueuePacket(new PACKET_MESSAGE("This user has this homework set and it's not done yet."));
									break;
								}
							}
						}

						stmt = cDatabase::Get()->PrepareQuery("INSERT INTO homework (userid, testid, dateset, datedue, resultid) VALUES (?, ?, ?, ?, NULL)");
						if(stmt != nullptr) {
							stmt->bindInt( 1, userid );
							stmt->bindInt( 2, testid );
							stmt->bindInt( 3, (int)time(0) );
							stmt->bindInt( 4, datedue + (int)time(0) );
							stmt->execute();
						}
					
					}
					break;
				case CPKT_SELF_TESTS_ID:
					{
						bool homework = conn->readByte() != 0;
						cStream str;

						str.writeByte(13);

						if(homework) {
							cDBStatement* stmt = cDatabase::Get()->PrepareQuery(
								"SELECT t1.testid, t2.title, t1.datedue, t3.score "
								"FROM homework t1, tests t2 "
								"LEFT OUTER JOIN (SELECT id, MAX(score) AS score FROM testresults) t3 ON t1.resultid = t3.id "
								"WHERE t2.id = t1.testid and t1.userid = ?");
							if(stmt != nullptr) {
								stmt->bindInt( 1, (int)conn->userid );
								DBResult res = stmt->execute();
								delete stmt;
								str.writeInt(res.GetRows());
								for( int i = 0; i <res.GetRows(); i++) {
									str.writeInt(atoi(res.data[i][0].c_str()));
									str.writeString(res.data[i][1]);
									if(res.isNull[i][3]) {
										str.writeByte(0); // not done
										str.writeInt(atoi(res.data[i][2].c_str()));
										str.writeShort(0);
									}else{
										str.writeByte(1); // done
										str.writeInt(0);
										str.writeShort((unsigned short)atoi(res.data[i][3].c_str()));
									}
								}
							}
						}else{
							cDBStatement* stmt = cDatabase::Get()->PrepareQuery(
								"SELECT t1.id, t1.title, t3.datedue, t2.score "
								"FROM tests t1 "
								"LEFT OUTER JOIN (SELECT id, testid, MAX(score) as score FROM testresults WHERE userid = ? GROUP BY testid)  t2 ON t1.id = t2.testid "
								"LEFT OUTER JOIN homework t3 ON t3.testid = t1.id AND t3.userid = ?");
							if(stmt != nullptr) {
								stmt->bindInt( 1, (int)conn->userid );
								stmt->bindInt( 2, (int)conn->userid );
								DBResult res = stmt->execute();
								delete stmt;
								str.writeInt(res.GetRows());
								for( int i = 0; i <res.GetRows(); i++) {
									str.writeInt(atoi(res.data[i][0].c_str()));
									str.writeString( res.data[i][1] ); 
									str.writeByte( res.isNull[i][3] ? 0 : 1 ); // is done; score not null => done
												// if no due date => NOT DUE else if no score => DUE else => NOT DUE
									str.writeInt( res.isNull[i][2] ?  0 : res.isNull[i][3] ? atoi(res.data[i][2].c_str()) : 0 ); 
									str.writeShort( (unsigned short)(res.isNull[i][3] ? 0 : atoi(res.data[i][3].c_str())) );
								}
							}
						}

						cRawPacket* pkt = new cRawPacket(str);
						conn->QueuePacket(pkt);
					}
					break;
				case CPKT_TEST_GET:
					{
						int testid = conn->readInt();


						cStream str;
						str.writeByte(12);

						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("SELECT title FROM tests WHERE id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							DBResult res = stmt->execute();
							delete stmt;
							if(res.GetRows() > 0)
								str.writeString(res.data[0][0]);
							else {
								conn->QueuePacket(new PACKET_MESSAGE("This test doesn't exist anymore!"));
								break;
							}
						}

						stmt = cDatabase::Get()->PrepareQuery("SELECT id, question, answer FROM testquestions WHERE testid = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							DBResult res = stmt->execute();
							delete stmt;
							str.writeInt(res.GetRows());
							for(int i = 0; i < res.GetRows(); i++) {
								str.writeInt(atoi(res.data[i][0].c_str()));
								str.writeString(res.data[i][1]);
								char* p = (char*)malloc(res.data[i][2].length()+1);
								memcpy( p, res.data[i][2].c_str(), res.data[i][2].length() );
								p[res.data[i][2].length()] = '\0';
								char* q = p;
								for(int j = 0; j < 4; j++) {
									while( *q != '\0' && *q != ';' ) q++;
									if(*q == 0)
										j = 4;
									*q = 0;
									str.writeString(std::string( p ));
									q++;
									p=q;
								}

							}
						}

						cRawPacket* pkt = new cRawPacket(str);
						conn->QueuePacket(pkt);
					}
					break;
				case CPKT_UPDATE_SCORE_ID:
					{
						int testid = conn->readInt();
						int score = conn->readShort();

						if(!conn->loggedIn)
							break;


						cDBStatement* stmt = cDatabase::Get()->PrepareQuery("SELECT 1 FROM tests WHERE id = ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							DBResult res = stmt->execute();
							delete stmt;
							if(res.GetRows() <= 0) {
								conn->QueuePacket(new PACKET_MESSAGE("This test doesn't exist anymore!"));
								break;
							}
						}

						stmt = cDatabase::Get()->PrepareQuery("INSERT INTO testresults (userid, testid, datetaken, score) VALUES (?, ?, ?, ?)");
						if(stmt != nullptr) {
							stmt->bindInt( 1, (int)conn->userid );
							stmt->bindInt( 2, testid );
							stmt->bindInt( 3, (int)time(0) );
							stmt->bindInt( 4, score );
							stmt->execute();
						}


						// get the result id
						int resultid = 0;
						stmt = cDatabase::Get()->PrepareQuery("SELECT id FROM testresults WHERE testid = ? AND userid = ? AND datetaken > ?");
						if(stmt != nullptr) {
							stmt->bindInt( 1, testid );
							stmt->bindInt( 2, (int)conn->userid );
							stmt->bindInt( 3, (int)(time(0)-5) );
							DBResult res = stmt->execute();
							delete stmt;
							if(res.GetRows() <= 0) {
								LOG("error: test result was inserted but can't find it");
								break;
							}else{
								resultid = atoi(res.data[0][0].c_str());
							}
						}
						// try to find homework
						stmt = cDatabase::Get()->PrepareQuery("UPDATE homework SET resultid = ? WHERE userid = ? AND testid = ? AND resultid IS NULL");
						if(stmt != nullptr) {
							stmt->bindInt( 1, resultid );
							stmt->bindInt( 2, (int)conn->userid );
							stmt->bindInt( 3, testid );
							stmt->execute();
						}

					}
					break;
				case CPKT_CHAT_SEND_ID:
					{
						std::string sender_name = conn->username;
						std::string message = conn->readString();
						
						if(!conn->chatAccess) {
							conn->QueuePacket(new PACKET_MESSAGE("Your right to write in chat has been revoked. Contact your teacher if you belive this shouldn't happen. Your message has not been sent."));
							break;
						}
						if(message.length() > 1200) {
							conn->QueuePacket(new PACKET_MESSAGE("Your message is too long. It was not sent."));
							break;
						}

						cStream stream;
						stream.writeByte(6);
						stream.writeInt((int)time(0));
						stream.writeString(conn->fullname);
						stream.writeString(message);
						for(size_t i = 0; i < connections.size(); i++) {
							cConnection* c = connections[i];
							if(c->Closed || c->userid == conn->userid)
								continue;
							c->QueuePacket(new cRawPacket( stream ));
						}
						cStream selfstream;
						selfstream.writeByte(6);
						selfstream.writeInt((int)time(0));
						selfstream.writeString("You");
						selfstream.writeString(message);
						conn->QueuePacket(new cRawPacket( selfstream ));
					}
					break;
				default:
					LOG("Received unknown packet (%d) from %s!\n", packet_id, conn->GetSocketString().c_str());
					conn->ClearQueue();
					conn->QueuePacket(new PACKET_DISCONNECT("Internal system error has occured. If the problem persists, please contact your system administrator."));
					conn->QueuePacket(new PACKET_TERMINATOR());
					break;
				}
				

			}
		}

	}
}